package com.jeasonzhao.commons.db.helper;

import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.SQLException;

public class ConnectionHelper
{
    public static Connection connectOracle(String strServerAddress,int nPort,
                                           String strSchema,
                                           String strUserName,String strPassword)
        throws SQLException
    {
        return connect(
            "oracle.jdbc.driver.OracleDriver",
            "jdbc:oracle:thin:@" + strServerAddress + ":" + (nPort > 0 ? nPort : 1521) + ":" + strSchema,
            strUserName,
            strPassword
            );
    }

    public static Connection connectOracle(String strServerAddress,
                                           String strSchema,
                                           String strUserName,String strPassword)
        throws SQLException
    {
        return connectOracle(strServerAddress, -1,strSchema,
                             strUserName,
                             strPassword
            );
    }

    public static Connection connectODBC(String strDsn,String strUserName,String strPassword)
        throws SQLException
    {
        return connect("sun.jdbc.odbc.JdbcOdbcDriver",
                       "jdbc:odbc:" + strDsn,
                       strUserName,strPassword);
    }

    public static Connection connectSqlServer(String strServerAdress,
                                              int nPort,String strCatalog,
                                              String strUserName,String strPassword)
        throws SQLException
    {
        boolean isSqlServer2005 = true;
        try
        {
            isSqlServer2005 = null != Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
        }
        catch(Exception excep)
        {
            /**Do not throw exception here!!**/
            isSqlServer2005 = false;
        }
        if(isSqlServer2005)
        {
            return connect("com.microsoft.sqlserver.jdbc.SQLServerDriver",
                           "jdbc:sqlserver://" + strServerAdress + ":" +
                           (nPort < 0 ? 1433 : nPort) + ";databaseName=" + strCatalog,
                           strUserName,strPassword);
        }
        else
        {
            return connect("com.microsoft.jdbc.sqlserver.SQLServerDriver",
                           "jdbc:microsoft:sqlserver://" + strServerAdress + ":" +
                           (nPort < 0 ? 1433 : nPort) + ";databaseName=" + strCatalog,
                           strUserName,strPassword);

        }
    }

    public static Connection connectSqlServer(String strServerAdress,
                                              String strCatalog,
                                              String strUserName,String strPassword)
        throws SQLException
    {
        return connectSqlServer(strServerAdress, -1,strCatalog,
                                strUserName,strPassword);
    }

    public static Connection connectMySql(String strServerAddress,
                                          String strDatabaseName,String strUserName,String strPassword,
                                          String strCharSet)
        throws SQLException
    {
        return connectMySql(strServerAddress, -1,strDatabaseName,strUserName,strPassword);
    }

    public static Connection connectMySql(String strServerAddress,
                                          String strDatabaseName,String strUserName,String strPassword)
        throws SQLException
    {
        return connectMySql(strServerAddress, -1,strDatabaseName,strUserName,strPassword);
    }

    public static Connection connectSybase(
        String strServerAddress,
        String strSchema,
        String strUserName,String strPassword)
        throws SQLException
    {
        return connectSybase(strServerAddress, -1,strSchema,strUserName,strPassword);
    }

    public static Connection connectSybase(
        String strServerAddress,int nPort,
        String strSchema,
        String strUserName,String strPassword)
        throws SQLException
    {
        return connect("com.sybase.jdbc.SybDriver",
                       "dbc:sybase:Tds:" + strServerAddress + ":" + (nPort < 0 ? 2642 : nPort) + "/" + strSchema,
                       strUserName,strPassword);
    }

    public static Connection connectDB2(
        String strServerAddress,int nPort,
        String strSchema,
        String strUserName,String strPassword)
        throws SQLException
    {
        return connect("COM.ibm.db2.jdbc.app.DB2Driver",
                       "jdbc:db2:" + strServerAddress + ":" + (nPort < 0 ? 2642 : nPort) + "/" + strSchema,
                       strUserName,strPassword);
    }

    public static Connection connectPostgreSql(
        String strServerAddress,
        String strSchema,
        String strUserName,String strPassword)
        throws SQLException
    {
        return connect("org.postgresql.Driver",
                       "jdbc: postgresql://" + strServerAddress + "/" + strSchema,
                       strUserName,strPassword);
    }

    public static Connection connectMySql(String strServerAddress,int nPort
                                          ,String strDatabaseName,String strUserName,String strPassword)
        throws SQLException
    {
        return connect("com.mysql.jdbc.Driver",
                       "jdbc:mysql://" + strServerAddress + ":" + (nPort < 0 ? 3306 : nPort) + "/" + strDatabaseName,
                       strUserName,strPassword);
    }

    public static Connection connectSqlite(String strFileName)
        throws SQLException
    {
        if(null == strFileName || strFileName.trim().length() < 1)
        {
            throw new SQLException("Can not connect to null database, the filename must not be NULL");
        }
        return connect("org.sqlite.JDBC","jdbc:sqlite://" + strFileName,null,null);
    }

    public static Connection connect(String strDriver,String strUrl,
                                     String strUserName,String strPassword)
        throws SQLException
    {
        try
        {
            Driver mydriver = (Driver) Class.forName(strDriver).newInstance();
            DriverManager.registerDriver((Driver) mydriver);
        }
        catch(ClassNotFoundException excep)
        {
            throw new SQLException("The Driver class [" + strDriver +
                                   "]Can not be found, please check you classpath. " + excep.getMessage());
        }
        catch(SQLException excep)
        {
            throw excep;
        }
        catch(Exception excep)
        {
            throw new SQLException("Driver class [" + strDriver + "] was failed to inilized." +
                                   excep.getMessage());
        }
        return DriverManager.getConnection(strUrl,strUserName,strPassword);
    }

    public static Connection connectEx(String strDatabaseName,String strUserName,String strPassword
                                       ,String ...params)
        throws SQLException
    {
        return ConnectionStringHelper.getInstance().connect(strDatabaseName,strUserName,strPassword,params);
    }
}
